/*
Run this script on:

        mssql-tl.db.jwan.it,14444.TTTLines2013    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 02/04/2014 12:16:36

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[fn_jb_check_abbonamenti_validi]'
GO
-- =====================================================================
-- Author:		Andrea Marchesini
-- Create date: 2012-09-18
-- Description:	Controlla che non ci siano rinnovi abbonamenti 
--  validi per la tessera nel periodo specificato
-- =====================================================================
CREATE FUNCTION [dbo].[fn_jb_check_abbonamenti_validi] 
(
	@id_tessera int,
	@from date
)
RETURNS bit
WITH ENCRYPTION AS
BEGIN
	DECLARE @c int
		
	SELECT @c= ISNULL((SELECT count(*) FROM jb_abbonamenti WHERE @id_tessera=id_tessera AND @from <= data_fine AND annullato=0),0)

	IF @c>0
		RETURN 1
	
	RETURN 0

END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[fn_jb_isCARTA]'
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[fn_jb_isCARTA](@formapag nvarchar(5))
RETURNS bit 
--WITH ENCRYPTION
WITH ENCRYPTION AS
begin
declare @res bit
SELECT @res=CARTACREDITO FROM PAGAME WHERE formapag=@formapag
return @res
end
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[fn_jb_isCONTANTI]'
GO
CREATE FUNCTION [dbo].[fn_jb_isCONTANTI](@formapag nvarchar(5))
RETURNS bit 
--WITH ENCRYPTION
WITH ENCRYPTION AS
begin
declare @res bit
SELECT @res=CASH FROM PAGAME WHERE formapag=@formapag
return @res
end
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[fn_jb_isVOUCHER]'
GO
CREATE FUNCTION [dbo].[fn_jb_isVOUCHER](@formapag nvarchar(5))
RETURNS bit 
--WITH ENCRYPTION
WITH ENCRYPTION AS
begin
declare @res bit
SELECT @res=VOUCHER FROM PAGAME WHERE formapag=@formapag
return @res
end
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[fn_jb_tessere_check_itiner_porti]'
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		Andrea Marchesini
-- Create date: 2012-09-18
-- Description:	Controlla se il codici associati alla tessera sono
--	porti o itinerari validi
-- =============================================
CREATE FUNCTION [dbo].[fn_jb_tessere_check_itiner_porti](
	@iti_port char(4)
)
RETURNS bit
WITH ENCRYPTION AS
BEGIN
	
	declare @c int
	
	select @c=isnull((select count(*) from itiner where coditinera = @iti_port),0)
	
	if @c > 0
		return 1
	
	select @c=isnull((select count(*) from porti where codporto = @iti_port),0)
	
	if @c > 0
		return 1
		
	return 0
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[fn_jb_config_value]'
GO
-- =============================================
-- Author:	Andrea Marchesini
-- Create date: 2012-09-25
-- Description:	Restituisce il valore della chiave di configurazione specificata
-- =============================================
CREATE FUNCTION [dbo].[fn_jb_config_value]
(
	@chiave nvarchar(50)
)
RETURNS nvarchar(100)
WITH ENCRYPTION AS
BEGIN
	RETURN (
		SELECT valore 
		FROM jb_personalizzazioni
		WHERE chiave = @chiave
	)

	
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
